IF EXISTS (SELECT *
           FROM   INFORMATION_SCHEMA.ROUTINES 
           WHERE  routine_name = 'sp_GetArticles'
           AND    routine_type = 'Procedure'
           AND    routine_schema = 'dbo' )
BEGIN
   DROP PROCEDURE dbo.sp_GetArticles
   IF OBJECT_ID('dbo.sp_GetArticles') IS NULL
      PRINT 'DROPPED PROCEDURE dbo.sp_GetArticles'
   ELSE
      PRINT 'FAILED DROPPING PROCEDURE dbo.sp_GetArticles'
END
GO

CREATE PROCEDURE dbo.sp_GetArticles @CategoryId     INT
                                   ,@OriginAuthor   NVARCHAR(100)
                                   ,@RecommendBy    NVARCHAR(100)
                                   ,@Title          NVARCHAR(255)
                                   ,@PartnerID      INT
                                   ,@Status         VARCHAR(10)
/*****************************************************************/
/* SOW Version 1.0.x                                             */
/*                                                               */
/* Copyright @2005 Spring of Water.      			 */
/*                                                               */
/* 04/21/2005                                                    */
/*                                                               */
/*  The sp performs the following functionality. 		 */
/*  Display latest articles on main page                         */
/*  								 */
/*                                                               */
/* ============================================================= */
/* Modification History                                          */
/* ============================================================= */
/* Date:      Developer:       Comment:                          */
/* -----      ----------       --------                          */
/*****************************************************************/
AS

   DECLARE @SelectSQL   VARCHAR(4000)
          ,@WHERE       INT

   SELECT @SelectSQL = ''
   SELECT @WHERE = 0

   SELECT @SelectSQL = 'SELECT * FROM Word '
   IF ISNULL(@CategoryId, 0) > 0
   BEGIN
      SELECT @SelectSQL = @SelectSQL + 
      'WHERE CategoryId = ' + CAST(@CategoryId AS VARCHAR(20))

      SET @WHERE = 1
   END

   IF LEN(ISNULL(@OriginAuthor, '')) > 0
      IF @WHERE = 0
      BEGIN
         SELECT @SelectSQL = @SelectSQL + 
         'WHERE OriginAuthor LIKE ''%' + @OriginAuthor + '%'''

         SET @WHERE = 1
      END
      ELSE
         SELECT @SelectSQL = @SelectSQL + 
         'AND OriginAuthor LIKE ''%' + @OriginAuthor + '%'''

   IF LEN(ISNULL(@RecommendBy, '')) > 0
      IF @WHERE = 0
      BEGIN
         SELECT @SelectSQL = @SelectSQL + 
         'WHERE RecommendBy LIKE ''%' + @RecommendBy + '%'''

         SET @WHERE = 1
      END
      ELSE
         SELECT @SelectSQL = @SelectSQL + 
         'AND RecommendBy LIKE ''%' + @RecommendBy + '%'''

   IF LEN(ISNULL(@Title, '')) > 0
      IF @WHERE = 0
      BEGIN
         SELECT @SelectSQL = @SelectSQL + 
         'WHERE Title LIKE ''%' + @Title + '%'''

         SET @WHERE = 1
      END
      ELSE
         SELECT @SelectSQL = @SelectSQL + 
         'AND Title LIKE ''%' + @Title + '%'''

   IF ISNULL(@PartnerID, 0) > 0
      IF @WHERE = 0
      BEGIN
         SELECT @SelectSQL = @SelectSQL + 
        'WHERE PartnerID = ' + CAST(@PartnerID AS VARCHAR(20))

         SET @WHERE = 1
      END
      ELSE
         SELECT @SelectSQL = @SelectSQL + 
        'AND PartnerID = ' + CAST(@PartnerID AS VARCHAR(20))

   IF LEN(ISNULL(@Status, '')) > 0
      IF @WHERE = 0
      BEGIN
         SELECT @SelectSQL = @SelectSQL + 
         'WHERE Status LIKE ''%' + @Status + '%'''

         SET @WHERE = 1
      END
      ELSE
         SELECT @SelectSQL = @SelectSQL + 
         'AND Status LIKE ''%' + @Status + '%'''

   EXEC(@SelectSQL)
GO
